package com.roc.web.action.terminal;

import java.util.ArrayList;
import java.util.List;

import com.roc.page.BaseSearchCondition;
import com.roc.page.WhereHQLFieldBean;
import com.roc.util.Constance;

public class TermSelfSupportActivateStatisticsSearchCondition extends BaseSearchCondition  {	
	
	private String  agentId;// 一级代理商
	private String  agentName;// 一级代理商名称
	
	private String  serialNum ;// SN
	private String  posBrand ;// 厂商
	private String  startBrandDate ;// 绑定开始时间(年月日)
	private String  endBrandDate ;// 绑定结束时间(年月日)
	private String  startTrnDate ;// 激活开始时间(年月日)
	private String  endTrnDate ;// 激活结束时间(年月日)
	
	private String obj;
	private String orgCode;// 机构号

	private String effectAct;// 统计类型 1.激活统计，2.有效商户激活统计
	
	@Override
	protected List<WhereHQLFieldBean> getWhereHQLFieldBeanList() {
		List<WhereHQLFieldBean> filedBeanList = new ArrayList<WhereHQLFieldBean>();
	/*
		if (Constance.isNotEmpty(agentId))
		{
			filedBeanList.add(new WhereHQLFieldBean("TT.AGENT_ID", agentId.trim(), "=", false));
		}*/
		if (Constance.isNotEmpty(agentName))
		{
			filedBeanList.add(new WhereHQLFieldBean("TR.oneagent_name", agentName.trim(), "=", false));
		}
		// SN 	
		if (Constance.isNotEmpty(serialNum))
		{
			filedBeanList.add(new WhereHQLFieldBean("TR.serial_num", serialNum.trim(), "=", false));
		}
		// 厂商
		if (Constance.isNotEmpty(posBrand) && !posBrand.equals("-1")) {
			filedBeanList.add(new WhereHQLFieldBean("TR.pos_brand", posBrand.trim(), "=", false));
		}	
		// 绑定日期 
		if (Constance.isNotEmpty(startBrandDate) && Constance.isNotEmpty(endBrandDate)) {
			String startDate = startBrandDate.replace("-", "");
			String endDate = endBrandDate.replace("-", "");
			
			filedBeanList.add(new WhereHQLFieldBean(
					"to_char(TR.brand_date,'yyyyMMdd')", startDate, ">=",
					false));
			filedBeanList
					.add(new WhereHQLFieldBean(
							"to_char(TR.brand_date,'yyyyMMdd')", endDate,
							"<=", false));
		}
		
		// 激活日期
		if (Constance.isNotEmpty(startTrnDate) && Constance.isNotEmpty(endTrnDate)) {
			String startDate = startTrnDate.replace("-", "");
			String endDate = endTrnDate.replace("-", "");
		//	startDate = startDate.length() == 7 ?startDate + "0" : startDate;
		//	endDate = endDate.length() == 7 ?endDate + "0" : endDate;
			
			filedBeanList.add(new WhereHQLFieldBean(
					"to_char(TR.trn_date)", startDate, ">=",
					false));
			filedBeanList
					.add(new WhereHQLFieldBean(
							"to_char(TR.trn_date)", endDate,
							"<=", false));
		}

		return filedBeanList;
	}

	@Override
	protected String getRowCountHQL() {
		StringBuffer sb=new StringBuffer();

		sb.append( " select COUNT(1) from ( " );
		sb.append( " 		select TS.*,  " );
		sb.append( "             TMP.*,  " );
		sb.append( "             (select T4.real_name from T_OPERATORS T4 where T4.id = TMP.ONEAGENTID) oneagent_name,  " );
		sb.append( "             (select T4.real_name from T_OPERATORS T4 where T4.id = TMP.COMPANYID) company_name  " );
		sb.append( "   		from (select * " );
		sb.append( "          	 from t_term_activate_statistics t " );
		sb.append( "         	 where t.status = '" + effectAct + "' " );
		sb.append( "        	    and exists " );
		sb.append( "        	  (select 1 from t_sn s where t.serial_num = s.serial_num)) TS, " );
		sb.append( "         " );
		sb.append( "       	 (SELECT TO_CHAR(T.ID) TID, t.real_name, t.ONEAGENTID, t.COMPANYID " );
		sb.append( "      	     FROM T_OPERATORS T " );
		sb.append( "       	   START WITH ID = " + obj + " " );
		sb.append( "      	   CONNECT BY PRIOR T.ID = T.SUPRI_ID " );
		sb.append( "      	          AND T.ROLE_TYPE = '03') TMP " );
		sb.append( "  	where TMP.tId = TS.AGENT_ID " );
		sb.append( " ) TR where 1 = 1 " );

		return sb.toString();
	}

	@Override
	protected String getSearchHQL() {
		StringBuffer sb=new StringBuffer();

		sb.append( " select * from (  " );
		sb.append( " 		select TS.SERIAL_NUM,  " );
		sb.append( "             TS.POS_BRAND,  " );
		sb.append( "             TS.BRAND_DATE,  " );
		sb.append( "             TS.TRN_DATE,  " );
		sb.append( "             TS.DT_CREATE,  " );
		sb.append( "             TS.DT_LASTMOD,  " );
		sb.append( "             TMP.REAL_NAME,  " );
		sb.append( "             (select T4.real_name from T_OPERATORS T4 where T4.id = TMP.ONEAGENTID) oneagent_name,  " );
		sb.append( "             (select T4.real_name from T_OPERATORS T4 where T4.id = TMP.COMPANYID) company_name,  " );
		sb.append( "             TS.AGENT_ID,  " );
		sb.append( "             TS.MCHNT_CD,  " );
		sb.append( "             TS.TERM_ID,  " );
		sb.append( "             TMP.ONEAGENTID  " );
		sb.append( "   from (select * " );
		sb.append( "           from t_term_activate_statistics t " );
		sb.append( "          where t.status = '" + effectAct + "' " );
		sb.append( "            and exists " );
		sb.append( "          (select 1 from t_sn s where t.serial_num = s.serial_num)) TS, " );
		sb.append( "         " );
		sb.append( "        (SELECT TO_CHAR(T.ID) TID, t.real_name, t.ONEAGENTID, t.COMPANYID " );
		sb.append( "           FROM T_OPERATORS T " );
		sb.append( "          START WITH ID = " + obj + " " );
		sb.append( "         CONNECT BY PRIOR T.ID = T.SUPRI_ID " );
		sb.append( "                AND T.ROLE_TYPE = '03') TMP " );
		sb.append( "  where TMP.tId = TS.AGENT_ID " );
		sb.append( "  ) TR where 1 = 1 " );
		
		return sb.toString();
	}
	
	@Override
	protected String getOrderPartOfHQL() {
		return " order by TR.DT_CREATE ";
	}

	public String getAgentId() {
		return agentId;
	}

	public void setAgentId(String agentId) {
		this.agentId = agentId;
	}

	public String getObj() {
		return obj;
	}

	public void setObj(String obj) {
		this.obj = obj;
	}

	public String getOrgCode() {
		return orgCode;
	}

	public void setOrgCode(String orgCode) {
		this.orgCode = orgCode;
	}

	public String getAgentName() {
		return agentName;
	}

	public void setAgentName(String agentName) {
		this.agentName = agentName;
	}

	public String getSerialNum() {
		return serialNum;
	}

	public void setSerialNum(String serialNum) {
		this.serialNum = serialNum;
	}

	public String getPosBrand() {
		return posBrand;
	}

	public void setPosBrand(String posBrand) {
		this.posBrand = posBrand;
	}

	public String getStartBrandDate() {
		return startBrandDate;
	}

	public void setStartBrandDate(String startBrandDate) {
		this.startBrandDate = startBrandDate;
	}

	public String getEndBrandDate() {
		return endBrandDate;
	}

	public void setEndBrandDate(String endBrandDate) {
		this.endBrandDate = endBrandDate;
	}

	public String getStartTrnDate() {
		return startTrnDate;
	}

	public void setStartTrnDate(String startTrnDate) {
		this.startTrnDate = startTrnDate;
	}

	public String getEndTrnDate() {
		return endTrnDate;
	}

	public void setEndTrnDate(String endTrnDate) {
		this.endTrnDate = endTrnDate;
	}

	public String getEffectAct() {
		return effectAct;
	}

	public void setEffectAct(String effectAct) {
		this.effectAct = effectAct;
	}

}
